<?php
if(!defined('BASEPATH'))
    exit('No direct script access allowed');
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of masterrekening_model
 *
 * @author miyzan
 */
class generatepiutang_model extends MY_MODEL{
    //put your code here
    
    function __construct() {
        parent::__construct();
        // $this->load->database();
    }
    
    function get_rows_preview($thajaran,$thbl,$search="",$start,$limit) {
        $sql_search="";
        if ($search != "") {
            $sql_search .= "and (lower(nis) LIKE '" . strtolower($search) . "%' or lower(nama) LIKE '%" . strtolower($search) . "%' 
                 or lower(kelas) LIKE '" . strtolower($search) . "%'  or lower(tahun_ajaran) LIKE '" . strtolower($search) . "%'
                       or lower(keterangan) LIKE '" . strtolower($search) . "%')";
        }
        $select="select nis, nama, kd_kelas, kelas, id_th_ajaran, tahun_ajaran, 
            semester, kd_biaya, keterangan as biaya, biaya_dikenakan, $thbl as thbl,
                get_pot_keterangan(nis,kd_biaya,$thbl) as detail_potongan,
                    get_pot_potongan(nis,kd_biaya,$thbl) as total_potongan,
                    biaya_dikenakan-get_pot_potongan(nis,kd_biaya,$thbl) as total_biaya,
                    concat('[Siswa: ',nis,'-',nama,'] [',kelas,'] [Tahun Ajaran-Semester: ',tahun_ajaran,'-',semester,'] [Bulan Tagihan: ','$thbl',']') as fgroup    
                from  v_pre_generate where id_th_ajaran='$thajaran' and semester is not null $sql_search limit $start,$limit";   
        
        $selectall="select * from v_pre_generate where id_th_ajaran='$thajaran' and semester is not null";

        $results = $this->get_query_rows_paging($select,$selectall);
        return $results;  
    }  
    function get_rows_preview_nis($nis,$thajaran,$thbl,$search="",$start,$limit) {
        $sql_search="";
        if ($search != "") {
            $sql_search .= "and lower(keterangan) LIKE '" . strtolower($search) . "%'";
        }
        
        $select="select nis, nama, kd_kelas, kelas, id_th_ajaran, tahun_ajaran, 
            semester, kd_biaya, keterangan as biaya, biaya_dikenakan, $thbl as thbl,
                get_pot_keterangan(nis,kd_biaya,$thbl) as detail_potongan,
                    get_pot_potongan(nis,kd_biaya,$thbl) as total_potongan,
                    biaya_dikenakan-get_pot_potongan(nis,kd_biaya,$thbl) as total_biaya,
                    concat('[Siswa: ',nis,'-',nama,'] [',kelas,'] [Tahun Ajaran-Semester: ',tahun_ajaran,'-',semester,'] [Bulan Tagihan: ','$thbl',']') as fgroup    
                from  v_pre_generate where nis='$nis' and id_th_ajaran='$thajaran' and semester is not null $sql_search limit $start,$limit";   
        
        $selectall="select * from v_pre_generate where id_th_ajaran='$thajaran' and semester is not null";

        $results = $this->get_query_rows_paging($select,$selectall);
        return $results;  
    }  
    
    function get_rows_resgen($thajaran,$thbl=null,$search="",$start,$limit) {
        $sql_search="thbl = '$thbl' and id_th_ajaran = '$thajaran'";

        if ($search != "") {
            $sql_search .= " and (lower(nis) LIKE '" . strtolower($search) . "%' or lower(nama) LIKE '%" . strtolower($search) . "%' 
                 or lower(kelas) LIKE '" . strtolower($search) . "%'  or lower(tahun_ajaran) LIKE '" . strtolower($search) . "%'
                       or lower(biaya) LIKE '" . strtolower($search) . "%')";
        }
        
        $select="thbl, nis, nama, kd_kelas, kelas, id_th_ajaran, tahun_ajaran, semester, kd_biaya, biaya, biaya_dikenakan, detail_potongan, total_potongan, total_biaya, fgroup";   
        $order=array("nis", "asc");     
        $table="v_gen_piutang";
        
        $results = $this->get_rows_paging($sql_search,$start,$limit,$table,$select,$order);
        return $results;  
    }  
    function get_rows_resgen_nis($nis,$thajaran,$thbl=null,$search="",$start,$limit) {
        $sql_search="nis = '$nis' and thbl = '$thbl' and id_th_ajaran = '$thajaran'";
//        $sql_search=""
        if ($search != "") {
            $sql_search .= " and lower(biaya) LIKE '" . strtolower($search) . "%' ";
//            $this->db->where($sql_search, NULL);
        }
        $select="thbl, nis, nama, kd_kelas, kelas, id_th_ajaran, tahun_ajaran, semester, kd_biaya, biaya, biaya_dikenakan, detail_potongan, total_potongan, total_biaya, fgroup";   
        $order=array("nis", "asc");     
        $table="v_gen_piutang";
        
        $results = $this->get_rows_paging($sql_search,$start,$limit,$table,$select,$order);
        return $results;  
    }  
    
    function get_rows_entry($nis="",$th="") {        
        $sql_search = "nis = '$nis' and id_th_ajaran='$th'";        
        $select="nis,id_th_ajaran,kd_biaya,biaya,biaya_dasar,biaya_dikenakan";
        $order=array("nis,id_th_ajaran", "asc");     
        $table="v_biaya_siswa";
        
        $results = $this->get_rows_table($sql_search, $table, $select, $order);
        return $results;       
        
    }  
    function get_rows_potongan($nis,$thbl,$kdbiaya){
        $sql_search = "nis = '$nis' and kd_biaya='$kdbiaya' and thbl_mulai<=$thbl and thbl_selesai>=$thbl";        
        $select="nis,kd_biaya,biaya,potongan,keterangan,th_ajaran_mulai,thbl_mulai,thbl_selesai";
        $order=array("kd_biaya,thbl_mulai", "asc");     
        $table="v_potongan";
        
        $results = $this->get_rows_table($sql_search, $table, $select, $order);
        return $results;       
        
    }    
    
    function get_rows_resgenpotongan($nis,$thbl,$kdbiaya){
        $sql_search = "nis = '$nis' and kd_biaya = '$kdbiaya' and thbl = $thbl";        
        $select="thbl, nis, id_th_ajaran, kd_biaya, biaya, potongan, keterangan";
        $order=array("kd_biaya,thbl", "asc");     
        $table="v_gen_piutang_pot";
        
        $results = $this->get_rows_table($sql_search, $table, $select, $order);
        return $results;       
        
    }
    function get_rows_all() {
        $sql_search="";        
        $select="*";
        $order=array("nis,id_th_ajaran", "asc");     
        $table="v_biaya_siswa";
        
        $results = $this->get_rows_table($sql_search, $table, $select, $order);
        return $results;       
        
    }  
    function get_rows_combo() {
        $sql_search="";        
        $select="*";
        $order=array("nis,id_th_ajaran", "asc");     
        $table="v_biaya_siswa";
        
        $results = $this->get_rows_table($sql_search, $table, $select, $order);
        return $results;       
        
    }  
}

?>
